package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInOut;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import org.utils.AnalogConvertor_RU;
import extend.org.compiere.utils.Util;

public class MM_Form_Z2 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	private int p_Record_ID = 0, p_Accountant_ID, p_ReleaseAllowed_ID;
	
	Date p_DateDoc;
	
	String ResultPath;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	p_Record_ID = pi.getRecord_ID();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("M_InOut_ID"))
			p_Record_ID = para[i].getParameterAsInt();
		else if (name.equals("Accountant_ID"))
			p_Accountant_ID = para[i].getParameterAsInt();
		else if (name.equals("ReleaseAllowed_ID"))
			p_ReleaseAllowed_ID = para[i].getParameterAsInt();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Z2_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 9) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(0).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(9).getCell(21).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(56).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		MInOut ino = new MInOut(m_ctx, p_Record_ID, get_TrxName());
		sheet.getRow(12).getCell(44).setCellValue(ino.getDocumentNo());
		sheet.getRow(12).getCell(54).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(ino.getMovementDate())));
		
		sheet.getRow(17).getCell(0).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(17).getCell(16).setCellValue((new MBPartner(m_ctx , ino.getC_BPartner_ID(), get_TrxName()).getName()));
		sheet.getRow(17).getCell(32).setCellValue((new MBPartner(m_ctx, ino.getSalesRep().getC_BPartner_ID(), get_TrxName()).getName()));
		
		//Document Footer
		String sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_ReleaseAllowed_ID;
		int id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(26).getCell(7).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(26).getCell(22).setCellValue((new MBPartner(m_ctx, p_ReleaseAllowed_ID, get_TrxName()).getName()));
		
		sheet.getRow(29).getCell(17).setCellValue((new MBPartner(m_ctx, p_Accountant_ID, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + ino.getSalesRep().getC_BPartner_ID();
		id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(28).getCell(40).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(31).getCell(20).setCellValue((new MBPartner(m_ctx, ino.getSalesRep().getC_BPartner_ID(), get_TrxName()).getName()));
		
		//Positions
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT pro.Name AS product " 
							+ ", pro.Value AS productno "
							+ ", uom.Name AS uom "
							+ ", inoL.QtyEntered AS qty "
							+ ", round(ordL.PriceEntered, 2) AS priceentered"
							+ ", round(inoL.QtyEntered * ordL.PriceEntered, 2) AS summa "
							+ ", round(((inoL.QtyEntered * ordL.PriceEntered) / 100) * tax.Rate, 2) AS summaVat "
						+ "FROM M_InOut ino "
						+ "JOIN M_InOutLine inoL ON inoL.M_InOut_ID = ino.M_InOut_ID "
						+ "LEFT JOIN M_Product pro ON pro.M_Product_ID = inoL.M_Product_ID " 
						+ "LEFT JOIN C_UOM_Trl uom ON uom.C_UOM_ID = pro.C_UOM_ID "
						+ "JOIN C_Order ord ON ord.C_Order_ID = ino.C_Order_ID "
						+ "JOIN C_OrderLine ordL ON (ordL.C_Order_ID = ord.C_Order_ID AND ordL.M_Product_ID = pro.M_Product_ID) "
						+ "JOIN C_Tax tax ON tax.C_Tax_ID = ordL.C_Tax_ID "
						+ "WHERE ino.MovementType = 'C-' "
							+ "AND ino.DocStatus <> 'VO' "
							+ "AND ino.DocStatus <> 'RE' "
							+ "AND ino.M_InOut_ID = " + p_Record_ID;
		
		int row = 23;
		int no = 0, totalQty = 0;;
		double totalAmt = 0;
		
		try {
			ps = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = ps.executeQuery();
			
			while (rs.next()) {
				//Shifting and Merging
				sheet.shiftRows(row, row + 10, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 4, 19));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 20, 26));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 27, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 37));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 38, 43));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 44, 48));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 49, 56));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 57, 64));
				
				R = sheet.createRow(row);
				for(int i = 0; i <= 64; i++){
					C = R.createCell(i);
					C.setCellStyle(mergeStyle);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(++no);
				sheet.getRow(row).getCell(4).setCellValue(rs.getString("product"));
				sheet.getRow(row).getCell(20).setCellValue(rs.getString("productno"));
				sheet.getRow(row).getCell(27).setCellValue(rs.getString("uom"));
				sheet.getRow(row).getCell(32).setCellValue(rs.getInt("qty"));
				sheet.getRow(row).getCell(38).setCellValue(rs.getInt("qty"));
				totalQty += rs.getInt("qty");
				sheet.getRow(row).getCell(44).setCellValue(rs.getInt("priceentered"));
				sheet.getRow(row).getCell(49).setCellValue(rs.getDouble("summa"));
				totalAmt += rs.getDouble("summa");
				sheet.getRow(row).getCell(57).setCellValue(rs.getDouble("summavat"));
				
				row++;
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
		
		sheet.getRow(row + 1).getCell(20).setCellValue(AnalogConvertor_RU.toString(totalQty));
		sheet.getRow(row + 1).getCell(48).setCellValue(AnalogConvertor_RU.toString(totalAmt));
		
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}
}